﻿CREATE PROCEDURE reports.s_CollectorPTPs
AS

DECLARE @NoteDateStart datetime SET @NoteDateStart = dbo.Today(DATEADD(wk,-1,GETDATE()))
--SELECT @NoteDateStart = MAX(NoteDate0) FROM reports.t_CollectionActivity
PRINT 'Generating collector activity after '+convert(varchar,@NoteDateStart,101)

SET @NoteDateStart = '1/1/2000'

DELETE FROM reports.t_CollectionPTP WHERE NoteDate0 > @NoteDateStart

INSERT INTO reports.t_CollectionPTP
SELECT     Notes.NoteUser, Notes.NoteDate0, Notes.Count, Notes.OPTP, Notes.PPTP, Notes.BPTP,PaymentsAmount
--INTO reports.t_CollectionPTP
FROM
(	SELECT     NoteUser, NoteDate0, COUNT(*) AS Count, SUM(OPTP) AS OPTP, SUM(PPTP) AS PPTP, SUM(BPTP) AS BPTP,SUM(PaymentAmount)PaymentsAmount
	FROM
	(	SELECT N_1.LoanID, N_1.NoteUser, N_1.NoteDate0,
					SUM(CASE N_1.StatusID WHEN dbo.fs_StateID__Notes__PromisedToPay() THEN 1 ELSE 0 END) AS OPTP,
					SUM(CASE N_1.statusid WHEN dbo.fs_StateID__Notes__PaymentRecieved() THEN 1 ELSE 0 END) AS PPTP,
					SUM(CASE N_1.statusid WHEN dbo.fs_StateID__Notes__BrokenPromise() THEN 1 ELSE 0 END) AS BPTP,
					SUM(CASE WHEN DATEDIFF(dd,NoteDate0,DateDue)>0	AND
												N_1.statusid = dbo.fs_StateID__Notes__PaymentRecieved()
									 THEN dbo.PaymentsInDateRange_Amount(LoanID,NoteDate0+1,DateDue+1)
									 ELSE 0
							END)PaymentAmount
		FROM
		(	SELECT N.ID, N.LoanID, N.Note, N.StatusID, N.StatusDate, N.StatusUser, dbo.Today(N.DateDue) DateDue, N.DateDueBy, N.NoteTypeID, 
			N.NoteActionCodeID, N.Phone, N.PhoneCategoryID, N.NoteDate, N.IsTask, N.NoteUser, N.NoteDate0, N.NoteDate1, 
			N.IsResolved
			FROM          t_Note AS N WITH(nolock)INNER JOIN
			v_ADS_CollectorsBySAM AS C ON N.NoteUser = C.sAMAccountName WHERE (N.NoteDate0 > @NoteDateStart)
		) AS N_1
		GROUP BY N_1.LoanID, N_1.NoteUser, N_1.NoteDate0
	) AS T
	GROUP BY NoteUser, NoteDate0
) AS Notes

